/* GSExcelToManufacture.java
 *
 * Project/Product: APEP-JZCG
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   peng.ning	2008-10-24	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.hitrust.gs.action;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.struts.upload.FormFile;

import system.LoginContext;
import system.base.ProductOutCategory;

import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.business.GSProviderProcessor;
import com.hitrust.gs.dao.GSManufactureDAO;
import com.hitrust.gs.dao.GSProductOutCategoryDao;
import com.hitrust.gs.form.GSImportExcelForm;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.SQLLogger;
import com.hitrust.gs.lib.db.GSPreparedStatementFactory;
import com.hitrust.gs.service.GSBaseAction;
import com.hitrust.gs.service.GSBaseForm;
import com.hitrust.gs.util.RegulaUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.page.ResultProcess;

public class GSExcelToProductOutCategory extends GSBaseAction {

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.apep.jzcg.service.GSBaseAction#execute(com.apep.jzcg.service.GSBaseForm
	 * )
	 */

	public static final String SHEET_NAME = "产品信息";
	public static final String SEP = "%";
	public static String[] HEAD_NAMES = { "序号", "集中编码", "状态", "分类"};

	public boolean[] COL_CHECK = new boolean[4];

	
	ArrayList addList =null;
	ArrayList updateList = null;
	GSProviderProcessor ppro = new GSProviderProcessor();

	// 匹配不包括 ',",<,>,&的特殊字符
	public static final String regula = "[^'\"<>&]*";
	public static final String zhibao = "^\\d+$";


	public String execute(GSBaseForm aForm) throws Exception {
		GSImportExcelForm tGSImportExcelForm = (GSImportExcelForm) aForm;
		FormFile formFile = tGSImportExcelForm.getExcelfile();
		
		if (formFile != null) {
			ArrayList tList = loadDataFromExcel(formFile.getInputStream(),aForm);
			ResultProcess tProc = new ResultProcess();
			tProc.setResult(tList);
			aForm.getSession().setAttribute("productUpdatePriceExcel", tProc);
		}
		return GSBaseConstants.FORWARD_SUCCESS;
	}

	/**
	 * 从Excel中载入格式化数据，数据项之间以分隔符%隔开
	 * 
	 * @param aInputStream
	 * @param aPage
	 * @param aPageSize
	 * @return
	 * @throws Exception
	 */
	public ArrayList loadDataFromExcel(InputStream aInputStream,GSBaseForm aForm)
			throws Exception {
		addList = new ArrayList();
		updateList = new ArrayList();
        String returnStr="";
		ArrayList tPageDataList = new ArrayList();
		ArrayList concentricityCodeList=new ArrayList();
		ArrayList indexList=new ArrayList();
		Workbook tExcelWorkbook = null;
		LoginContext context = (LoginContext)aForm.getSession().getAttribute(
				GSBaseConstants.LOGIN);
		try {
			tExcelWorkbook = Workbook.getWorkbook(aInputStream);

			Sheet tExcelSheet = tExcelWorkbook.getSheet(SHEET_NAME);
			if (tExcelSheet == null) {
				aForm.getRequest().setAttribute("gohistory","true");	
				throw new GSException("批量导入产品信息失败:文件格式不正确,无法找到：" + SHEET_NAME);
			}
			for (int i = 1; i < tExcelSheet.getRows(); i++) {
				Cell tCells[] = tExcelSheet.getRow(i);
				String tRowText = "";
				int num = tCells.length;
				for (short j = 0; j < num; j++) {
					Cell tExcelCell = tCells[j];
					if (tExcelCell == null) {
						break;
					}
					String tCellValue = tExcelCell.getContents();
					if (tCellValue.length() == 0) {
						tCellValue = " ";
					}
					tRowText += tCellValue + SEP;
				}

				for (int a = num; a < HEAD_NAMES.length; a++) {
					tRowText += " " + SEP;
				}

				// 检查数据是否正确,如果和数据库中不符则将结果添加到末尾,从第二行开始
				
				tRowText = this.checkManufacture(tRowText, i - 1,concentricityCodeList,indexList);
				for (int tJ = 0; tJ < COL_CHECK.length; tJ++) {
					if (COL_CHECK[tJ] == false) {
						aForm.getRequest().setAttribute("gohistory","true");	
						throw new GSException(tRowText);
					}
				}


				GSLogWriter.info(tRowText);
				
				
			}
			Connection iConn=null;
			GSProductOutCategoryDao poc= new GSProductOutCategoryDao();
			try {					
				iConn = poc.getConnetion();
				if(addList.size()>0){
					for(int b=0;b<addList.size();b++){
						ProductOutCategory p=(ProductOutCategory)addList.get(b);
						p.setM_last_update_user_id(context.getLoginUserId());
						p.setM_last_update_user(context.getLoginUserName());
						p.setM_last_update_time(new framework.core.DateUtil().getFullString());
						p.setCreateUserId(context.getLoginUserId());
						p.setCreateUser(context.getLoginUserName());
						p.setCreateTime(new framework.core.DateUtil().getFullString());
						poc.insert(p, iConn);
					}
				}
				if(updateList.size()>0){
					for(int b=0;b<updateList.size();b++){
						ProductOutCategory p=(ProductOutCategory)updateList.get(b);
						p.setM_last_update_user_id(context.getLoginUserId());
						p.setM_last_update_user(context.getLoginUserName());
						p.setM_last_update_time(new framework.core.DateUtil().getFullString());
						poc.update(p, iConn);
					}
				}
				iConn.commit();
				returnStr="插入成功";
			} catch (Exception e) {
				iConn.rollback();
				returnStr="插入失败";
				poc.pushSQLError(e.toString());
				SQLLogger.log("error:" + e.toString());
				e.printStackTrace();
			} finally {
				iConn.close();
			}
			aForm.getRequest().setAttribute("gohistory","true");	
			throw new GSException(returnStr);
		} catch (BiffException e) {
			GSLogWriter.error(e.getMessage());
		} catch (IOException e) {
			GSLogWriter.error(e.getMessage());
		}
		finally {
			if (tExcelWorkbook != null)
				tExcelWorkbook.close();
		}
		return tPageDataList;
	}


	public String checkManufacture(String aRowText, int a,ArrayList concentricityCodeList,ArrayList indexList) throws Exception {
		ProductOutCategory manuview = new ProductOutCategory();
		for (int i = 0; i < COL_CHECK.length; i++) {
			COL_CHECK[i] = true;
		}

		String[] tRecorderColumns = aRowText.split(SEP);

		for (int i = 0; i < tRecorderColumns.length; i++) {
			if (!RegulaUtil.matchByString(tRecorderColumns[i], regula)) {
				COL_CHECK[i] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[i] + "不能为',\",<,>,&等特殊字符!";
			}
		}
		if (tRecorderColumns[0].trim().equals("")) {
			COL_CHECK[0] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[0]
					+ "不能为空！";
		}
		if (tRecorderColumns[1].trim().equals("")) {
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[1]
					+ "不能为空！";
		}
		if (tRecorderColumns[2].trim().equals("")) {
			COL_CHECK[2] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[2]
					+ "不能为空！";
		}else if(tRecorderColumns[2].trim().equals("1")||tRecorderColumns[2].trim().equals("0")){
			manuview.setStatus(tRecorderColumns[2].trim());
		}else{
			COL_CHECK[2] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[2]
					+ "值必须是1或者0！";
		}
		if (tRecorderColumns[3].trim().equals("")) {
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[3]
					+ "不能为空！";
		}


		GSManufactureDAO dao = new GSManufactureDAO();
		GSManufactureBean mbean = dao.getManufactureByConcentricityCode(
				tRecorderColumns[1], " select * from t_product ");
		if (mbean == null) {
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[1]
					+ "产品不存在！";
		}
		if(indexList.contains(tRecorderColumns[0].trim())){
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " 
					+ "同一次导入"+ HEAD_NAMES[0]+"不能重复!";
		}
		
		if(concentricityCodeList.contains(tRecorderColumns[1].trim())){
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[1] + ": " 
					+ "同一次导入"+ HEAD_NAMES[1]+"不能重复!";
		}
		manuview.setProductId(mbean.getId());
		
		
			Pattern pattern = Pattern.compile("^[0-9]*\\.?[0-9]{0,4}");
			Matcher isNum = pattern.matcher(tRecorderColumns[3].trim());
			if (!isNum.matches()) {
				COL_CHECK[3] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[3] + "必须为数值!";
			} else if (Double.parseDouble(tRecorderColumns[3].trim()) < 1||Double.parseDouble(tRecorderColumns[3].trim()) > 9) {
				COL_CHECK[3] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[3] + "必须是1到9之间";
			}
		manuview.setOutCategory("0131001000"+tRecorderColumns[3].trim());
		GSProductOutCategoryDao pDao= new GSProductOutCategoryDao();
		if(pDao.findProductBidById(tRecorderColumns[1])==false){
			if(tRecorderColumns[2].trim().equals("0")){
				COL_CHECK[2] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": " 
				+ "数据不存在"+ HEAD_NAMES[2]+"不能设置无效!";
			}else{
				addList.add(manuview);
			}
		}else{
			 updateList.add(manuview);
		}
		
		concentricityCodeList.add(tRecorderColumns[1].trim());
		indexList.add(tRecorderColumns[0].trim());
		return aRowText + "匹配成功！";
	}
}
